# student_system.py
import streamlit as st
import sqlite3
import pandas as pd
import plotly.express as px
from datetime import date
from sklearn.linear_model import LinearRegression
import time
import markdown

# 定义默认报告模板
default_templates = [
    {
        "name": "标准薪资预测报告",
        "description": "基本的薪资预测报告，包含个人信息和预测结果",
        "content": """
# 工科毕业生薪资预测报告

## 个人信息
- **姓名**: {{name}}
- **性别**: {{gender}}
- **年龄**: {{age}}岁
- **教育水平**: {{education_level}}
- **成绩百分比**: {{percentage}}%
- **实习经验**: {{experience}}个月

## 技能评估
- 英语能力: {{english}}/10
- 编程能力: {{programming}}/10
- 分析能力: {{analytical}}/10
- 团队协作: {{teamwork}}/10
- 沟通能力: {{communication}}/10
- 解决问题能力: {{problem_solving}}/10

**综合技能评分**: {{skills_avg}}/10

## 薪资预测结果
根据您提供的信息，我们预测您的起始年薪为:

### ¥ {{predicted_salary}}

该薪资水平{{market_comparison}}当前市场平均水平。

*预测日期: {{prediction_date}}*

---
*注: 本预测基于历史数据和当前市场趋势，实际薪资可能因公司规模、地区和其他因素而有所不同。*
"""
    },
    {
        "name": "详细分析报告",
        "description": "包含详细分析和市场比较的综合报告",
        "content": """
# 工科毕业生薪资预测分析报告

## 1. 个人基本信息

|  项目  |  详情  |
|--------|--------|
| 姓名 | {{name}} |
| 性别 | {{gender}} |
| 年龄 | {{age}}岁 |
| 教育水平 | {{education_level}} |
| 学业成绩 | {{percentage}}% |
| 实习经验 | {{experience}}个月 |

## 2. 专业技能评估

<div style="background-color: #f8f9fa; padding: 15px; border-radius: 5px;">
<h3>技能评分 (1-10分)</h3>
<ul>
<li><strong>英语能力:</strong> {{english}}/10</li>
<li><strong>编程能力:</strong> {{programming}}/10</li>
<li><strong>分析能力:</strong> {{analytical}}/10</li>
<li><strong>团队协作:</strong> {{teamwork}}/10</li>
<li><strong>沟通能力:</strong> {{communication}}/10</li>
<li><strong>解决问题能力:</strong> {{problem_solving}}/10</li>
</ul>
<p><strong>综合技能评分:</strong> {{skills_avg}}/10</p>
</div>

## 3. 薪资预测分析

### 预测年薪: ¥ {{predicted_salary}}

#### 薪资构成分析:
- **基本薪资**: 根据行业基准
- **教育加成**: 基于{{education_level}}学历
- **成绩加成**: 基于{{percentage}}%的学业表现
- **经验加成**: 基于{{experience}}个月的实习经验
- **技能加成**: 基于{{skills_avg}}/10的综合技能评分

#### 市场比较:
您的预测薪资{{market_comparison}}当前工科毕业生的市场平均水平。在同等教育背景的毕业生中，您的预测薪资处于中上水平。

## 4. 提升建议

为进一步提高您的市场竞争力和薪资潜力，我们建议:

1. **继续提升专业技能**，特别是编程和分析能力
2. **积累更多实际项目经验**，尤其是与行业相关的实践
3. **考虑获取相关专业认证**，增强简历竞争力
4. **拓展行业人脉网络**，参加行业交流活动

## 5. 行业前景

工科毕业生在当前就业市场具有良好的发展前景，特别是在信息技术、人工智能、新能源等新兴领域。随着经验积累和技能提升，薪资有望在3-5年内实现显著增长。

---

*报告生成日期: {{prediction_date}}*

*免责声明: 本预测基于历史数据和当前市场趋势，仅供参考。实际薪资可能因公司规模、地区、经济环境等因素而有所不同。*
"""
    },
    {
        "name": "简洁版报告",
        "description": "简短的薪资预测摘要，适合快速查看",
        "content": """
# 薪资预测摘要

**{{name}}** ({{gender}}, {{age}}岁)

## 资质概述
- **学历**: {{education_level}}
- **成绩**: {{percentage}}%
- **实习**: {{experience}}个月
- **技能评分**: {{skills_avg}}/10

## 预测结果
**预测年薪**: ¥ {{predicted_salary}}

*{{prediction_date}}生成*
"""
    },
    {
        "name": "职业发展报告",
        "description": "包含职业发展路径和薪资增长预测的报告",
        "content": """
# 工科毕业生职业发展与薪资预测报告

## 个人档案
**姓名**: {{name}}  
**性别**: {{gender}}  
**年龄**: {{age}}岁  
**教育背景**: {{education_level}}  
**学业表现**: {{percentage}}%  
**实习经验**: {{experience}}个月  

## 当前能力评估
| 能力项 | 评分 | 行业均值 |
|-------|-----|---------|
| 英语能力 | {{english}}/10 | 7/10 |
| 编程能力 | {{programming}}/10 | 6/10 |
| 分析能力 | {{analytical}}/10 | 6/10 |
| 团队协作 | {{teamwork}}/10 | 7/10 |
| 沟通能力 | {{communication}}/10 | 7/10 |
| 解决问题 | {{problem_solving}}/10 | 6/10 |
| **综合评分** | **{{skills_avg}}/10** | **6.5/10** |

## 薪资预测
### 入职起始薪资: ¥ {{predicted_salary}}

### 薪资增长预测
| 工作年限 | 预计年薪 | 增长率 |
|---------|---------|-------|
| 入职起薪 | ¥ {{predicted_salary}} | - |
| 3年 | 约 ¥ {{predicted_salary}} × 1.4 | 40% |
| 5年 | 约 ¥ {{predicted_salary}} × 1.8 | 80% |
| 10年 | 约 ¥ {{predicted_salary}} × 2.5 | 150% |

## 职业发展路径
1. **初级阶段 (0-2年)**
   - 岗位: 初级工程师/技术员
   - 重点: 技术基础积累、项目实践
   - 建议: 专注核心技能提升，积极参与项目

2. **成长阶段 (3-5年)**
   - 岗位: 中级工程师/项目主管
   - 重点: 专业深化、团队协作
   - 建议: 考取专业认证，培养项目管理能力

3. **成熟阶段 (5-10年)**
   - 岗位: 高级工程师/技术经理
   - 重点: 技术领导力、跨部门协作
   - 建议: 拓展管理技能，建立行业影响力

## 能力提升建议
针对您的个人情况，我们建议重点提升以下能力:

1. **技术能力**: 深化专业知识，拓展相关技术领域
2. **项目管理**: 学习项目规划、资源调配和风险管理
3. **沟通表达**: 提升书面和口头表达能力，增强影响力
4. **持续学习**: 保持对新技术、新趋势的学习热情

---

*报告生成日期: {{prediction_date}}*

*注: 本报告基于当前信息预测，实际职业发展和薪资增长将受多种因素影响。*
"""
    },
    {
        "name": "竞争力分析报告",
        "description": "分析毕业生在就业市场中的竞争优势和劣势",
        "content": """
# 工科毕业生市场竞争力分析报告

<div style="text-align: center; padding: 20px; background-color: #f5f5f5;">
<h2>{{name}} 个人竞争力评估</h2>
<p>生成日期: {{prediction_date}}</p>
</div>

## 基本信息
- **姓名**: {{name}}
- **性别**: {{gender}}
- **年龄**: {{age}}岁
- **学历**: {{education_level}}
- **成绩**: {{percentage}}%
- **实习经验**: {{experience}}个月

## 市场竞争力评估
*以下为您在各维度的竞争力评分(1-10分)*

| 评估维度 | 个人得分 | 市场平均 |
|---------|---------|---------|
| 学历背景 | 8/10 | 7/10 |
| 学业成绩 | {{percentage}}/100 | 75/100 |
| 实践经验 | {{experience}}/60 | 12/60 |
| 英语能力 | {{english}}/10 | 6/10 |
| 专业技能 | {{programming}}/10 | 7/10 |
| 分析能力 | {{analytical}}/10 | 6/10 |
| 团队协作 | {{teamwork}}/10 | 7/10 |
| 沟通表达 | {{communication}}/10 | 6/10 |
| 问题解决 | {{problem_solving}}/10 | 7/10 |

**综合竞争力指数**: {{skills_avg}}/10

## 薪资市场定位
根据您的综合竞争力分析，我们预测您在当前就业市场的薪资定位为:

<div style="text-align: center; padding: 15px; background-color: #e8f4f8; border-radius: 5px; margin: 20px 0;">
<h3>预测年薪: ¥ {{predicted_salary}}</h3>
<p>市场定位: 中高端</p>
</div>

## 竞争优势分析
您的主要竞争优势在于:
1. **专业学历**: 这使您在求职初期具有较好的起点
2. **技术能力**: 有助于您在专业岗位上快速适应工作要求
3. **解决问题能力**: 为您提供了在复杂工作环境中的竞争力

## 提升空间
为进一步提高您的市场竞争力，建议重点提升:
1. **实践经验**: 通过实习、项目实践来加强
2. **行业认知**: 可以通过行业交流、专业会议进行提升
3. **领导能力**: 建议通过团队项目、志愿活动来改进

## 目标企业匹配度
根据您的个人特质和能力，以下类型企业可能是理想的就业目标:

| 企业类型 | 匹配度 | 薪资预期 | 发展空间 |
|---------|-------|---------|---------|
| 大型科技企业 | 4/5 | 高 | 4/5 |
| 创新型创业公司 | 3/5 | 中高 | 5/5 |
| 研究机构/高校 | 4/5 | 中 | 3/5 |

## 求职策略建议
1. **简历优化**: 突出您的专业技能和学习能力，强调解决问题的实际案例
2. **面试准备**: 准备充分的技术案例和项目经验，展示您的分析思维和沟通能力
3. **职业规划**: 短期内专注于技能积累和经验提升，长期发展可考虑技术专家或管理路线

---

*免责声明: 本报告基于您提供的信息和当前市场数据分析生成，仅供参考。实际就业情况可能受多种因素影响。*
"""
    }
]

# 数据库连接（复用已创建的数据库）
def connect_db():
    return sqlite3.connect("student_analysis.db")

# 添加数据处理函数定义
def data_processing_pipeline(raw_df):
    """标准化数据处理流程"""
    try:
        # 数据清洗
        processed_df = raw_df.copy()
        
        # 1. 空值处理（将-1转为NA）
        processed_df.replace(-1, pd.NA, inplace=True)
        
        # 2. 字段标准化
        if 'Board' in processed_df.columns:
            processed_df['Board'] = processed_df['Board'].str.upper()
        
        # 3. 日期字段转换
        date_columns = [col for col in processed_df.columns if 'Date' in col or 'DOB' in col]
        for col in date_columns:
            processed_df[col] = pd.to_datetime(processed_df[col], errors='coerce')
        
        # 4. 唯一性校验
        if 'StudentID' in processed_df.columns:
            if processed_df['StudentID'].duplicated().any():
                raise ValueError("发现重复学号，请检查数据源!")
        
        # 5. 数值类型转换
        numeric_cols = ['Percentage', 'Salary', 'English', 'Logical', 'Quant']
        for col in numeric_cols:
            if col in processed_df.columns:
                processed_df[col] = pd.to_numeric(processed_df[col], errors='coerce')
        
        return processed_df
        
    except Exception as e:
        raise RuntimeError(f"数据处理异常: {str(e)}") from e

# 进一步完善save_to_database函数，处理Cities、Colleges和PersonalityTraits表
def save_to_database(conn, df, mode="append"):
    """
    将DataFrame保存到数据库
    
    参数:
    conn - 数据库连接
    df - 要保存的DataFrame
    mode - 保存模式: "append"(追加) 或 "replace"(替换)
    """
    # 如果是替换模式，先清空相关表
    if mode == "replace":
        # 获取数据库中所有表
        cursor = conn.cursor()
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
        tables = [row[0] for row in cursor.fetchall()]
        
        # 清空相关表（按依赖关系倒序清空）
        related_tables = [
            "Salaries", "Skills", "Projects", "Certificates", 
            "Education", "Students", "Colleges", "DegreeSpecializations",
            "CollegePerformance", "PersonalityTraits", "Cities", "Boards"
        ]
        
        for table in related_tables:
            if table in tables:
                conn.execute(f"DELETE FROM {table}")
    
    # 获取数据库中所有表的结构
    table_columns = {}
    cursor = conn.cursor()
    
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
    all_tables = [row[0] for row in cursor.fetchall()]
    
    for table in all_tables:
        cursor.execute(f"PRAGMA table_info({table})")
        table_columns[table] = [row[1] for row in cursor.fetchall()]
    
    # 创建一个映射字典，将CSV列名映射到数据库表和列
    column_mappings = {
        # 基本映射
        "StudentID": {"table": "Students", "column": "StudentID"},
        "Gender": {"table": "Students", "column": "Gender"},
        "DOB": {"table": "Students", "column": "DOB"},
        "Name": {"table": "Students", "column": "Name"},
        "Phone": {"table": "Students", "column": "Phone"},
        "Email": {"table": "Students", "column": "Email"},
        "Address": {"table": "Students", "column": "Address"},
        
        # Education表映射
        "EducationLevel": {"table": "Education", "column": "EducationLevel"},
        "Percentage": {"table": "Education", "column": "Percentage"},
        "GraduationYear": {"table": "Education", "column": "GraduationYear"},
        
        # Colleges表映射
        "CollegeID": {"table": "Colleges", "column": "CollegeID"},
        "CollegeName": {"table": "Colleges", "column": "CollegeName"},
        "CollegeLocation": {"table": "Colleges", "column": "Location"},
        "Location": {"table": "Colleges", "column": "Location"},
        "City": {"table": "Colleges", "column": "Location"},
        
        # DegreeSpecializations表映射
        "Degree": {"table": "DegreeSpecializations", "column": "Degree"},
        "Specialization": {"table": "DegreeSpecializations", "column": "Specialization"},
        
        # Skills表映射
        "English": {"table": "Skills", "column": "English"},
        "Logical": {"table": "Skills", "column": "Logical"},
        "Quant": {"table": "Skills", "column": "Quant"},
        "Programming": {"table": "Skills", "column": "Programming"},
        "Analytical": {"table": "Skills", "column": "Analytical"},
        "Communication": {"table": "Skills", "column": "Communication"},
        
        # Salaries表映射
        "Salary": {"table": "Salaries", "column": "Salary"},
        "SalaryDate": {"table": "Salaries", "column": "Date"},
        
        # Cities表映射
        "CityName": {"table": "Cities", "column": "CityName"},
        "CityID": {"table": "Cities", "column": "CityID"},
        "State": {"table": "Cities", "column": "State"},
        "Country": {"table": "Cities", "column": "Country"},
        
        # CollegePerformance表映射
        "CollegePerformanceID": {"table": "CollegePerformance", "column": "CollegePerformanceID"},
        "CollegeID": {"table": "CollegePerformance", "column": "CollegeID"},
        "Ranking": {"table": "CollegePerformance", "column": "Ranking"},
        "PlacementPercentage": {"table": "CollegePerformance", "column": "PlacementPercentage"},
        "AverageSalary": {"table": "CollegePerformance", "column": "AverageSalary"},
        
        # PersonalityTraits表映射
        "Openness": {"table": "PersonalityTraits", "column": "Openness"},
        "Conscientiousness": {"table": "PersonalityTraits", "column": "Conscientiousness"},
        "Extraversion": {"table": "PersonalityTraits", "column": "Extraversion"},
        "Agreeableness": {"table": "PersonalityTraits", "column": "Agreeableness"},
        "Neuroticism": {"table": "PersonalityTraits", "column": "Neuroticism"},
        
        # 其他可能的映射
        "10percentage": {"table": "Education", "column": "Percentage", "condition": {"EducationLevel": "10th"}},
        "12percentage": {"table": "Education", "column": "Percentage", "condition": {"EducationLevel": "12th"}},
        "12graduation": {"table": "Education", "column": "GraduationYear", "condition": {"EducationLevel": "12th"}},
        "collegeID": {"table": "Colleges", "column": "CollegeID"},
    }
    
    # 动态添加CollegeTier列映射（如果Colleges表有CollegeTier列）
    if "Colleges" in table_columns and "CollegeTier" in table_columns["Colleges"]:
        column_mappings["CollegeTier"] = {"table": "Colleges", "column": "CollegeTier"}
        column_mappings["collegeTier"] = {"table": "Colleges", "column": "CollegeTier"}
    
    # 动态添加Year列映射（如果CollegePerformance表有Year列）
    if "CollegePerformance" in table_columns and "Year" in table_columns["CollegePerformance"]:
        column_mappings["Year"] = {"table": "CollegePerformance", "column": "Year"}
    
    # 动态添加Board列映射（如果Education表有Board列）
    if "Education" in table_columns and "Board" in table_columns["Education"]:
        column_mappings["Board"] = {"table": "Education", "column": "Board"}
        column_mappings["10board"] = {"table": "Education", "column": "Board", "condition": {"EducationLevel": "10th"}}
        column_mappings["12board"] = {"table": "Education", "column": "Board", "condition": {"EducationLevel": "12th"}}
    
    # 检查CSV中的列，并创建表数据结构
    tables_data = {}
    for col in df.columns:
        # 尝试找到映射
        mapping = column_mappings.get(col)
        if mapping:
            table_name = mapping["table"]
            column_name = mapping["column"]
            
            # 初始化表数据结构
            if table_name not in tables_data:
                tables_data[table_name] = {"columns": [], "data": []}
            
            # 添加列
            if column_name not in tables_data[table_name]["columns"]:
                tables_data[table_name]["columns"].append(column_name)
    
    # 特殊处理：如果有10percentage/12percentage等列，创建多条Education记录
    has_10th_data = any(col in df.columns for col in ["10percentage", "10board"])
    has_12th_data = any(col in df.columns for col in ["12percentage", "12board", "12graduation"])
    
    # 处理每一行数据
    for _, row in df.iterrows():
        try:
            # 为每个表准备数据
            row_tables_data = {table: {"columns": [], "values": []} for table in tables_data}
            
            # 处理StudentID（主键）
            student_id = None
            if "StudentID" in row:
                student_id = int(row["StudentID"])
            else:
                st.warning(f"跳过行: 缺少StudentID")
                continue
            
            # 处理基本映射
            for col in df.columns:
                mapping = column_mappings.get(col)
                if mapping and "condition" not in mapping:
                    table_name = mapping["table"]
                    column_name = mapping["column"]
                    
                    # 跳过空值
                    if pd.isna(row[col]):
                        continue
                    
                    # 跳过Education表的EducationLevel字段，我们将在后面单独处理
                    if table_name == "Education" and column_name == "EducationLevel":
                        continue
                    
                    # 添加列和值
                    if column_name not in row_tables_data[table_name]["columns"]:
                        row_tables_data[table_name]["columns"].append(column_name)
                        
                        # 根据列类型处理值
                        value = None
                        
                        # 特殊处理DOB日期字段
                        if column_name == "DOB":
                            if isinstance(row[col], str):
                                try:
                                    value = pd.to_datetime(row[col]).strftime('%Y-%m-%d')
                                except:
                                    value = "1990-01-01"  # 默认日期
                            elif isinstance(row[col], pd.Timestamp):
                                value = row[col].strftime('%Y-%m-%d')
                            else:
                                value = "1990-01-01"  # 默认日期
                        # 特殊处理其他日期字段
                        elif column_name == "Date":
                            if isinstance(row[col], str):
                                try:
                                    value = pd.to_datetime(row[col]).strftime('%Y-%m-%d')
                                except:
                                    value = date.today().strftime('%Y-%m-%d')  # 默认为今天
                            elif isinstance(row[col], pd.Timestamp):
                                value = row[col].strftime('%Y-%m-%d')
                            else:
                                value = date.today().strftime('%Y-%m-%d')  # 默认为今天
                        # 特殊处理字符串字段
                        elif column_name in ["Gender", "Name", "Phone", "Email", "Address", "CollegeName", "Degree", "Specialization"]:
                            if isinstance(row[col], str):
                                value = row[col]
                            else:
                                value = str(row[col])
                        # 特殊处理数值字段
                        elif column_name in ["Percentage", "Salary", "English", "Logical", "Quant", "Programming", "Analytical", "Communication", "Ranking", "PlacementPercentage", "AverageSalary", "Openness", "Conscientiousness", "Extraversion", "Agreeableness", "Neuroticism"]:
                            if pd.isna(row[col]):
                                value = 0.0
                            else:
                                value = float(row[col])
                        # 特殊处理整数字段
                        elif column_name in ["StudentID", "CollegeID", "GraduationYear", "CityID", "CollegePerformanceID"]:
                            if pd.isna(row[col]):
                                value = 0
                            else:
                                value = int(row[col])
                        # 其他字段
                        else:
                            if pd.isna(row[col]):
                                value = ""
                            else:
                                value = str(row[col])
                        
                        # 添加到表数据
                        row_tables_data[table_name]["values"].append(value)
            
            # 创建一个集合来跟踪已处理的教育级别
            processed_education_levels = set()
            
            # 处理Education表的数据 - 使用统一的方法处理所有教育级别
            if "Education" in all_tables:
                # 处理10th级别的教育数据
                if has_10th_data and ("10percentage" in row and not pd.isna(row["10percentage"]) or "10board" in row and not pd.isna(row["10board"])):
                    education_data = {"StudentID": student_id, "EducationLevel": "10th"}
                    
                    if "10percentage" in row and not pd.isna(row["10percentage"]):
                        education_data["Percentage"] = float(row["10percentage"])
                    
                    if "10board" in row and not pd.isna(row["10board"]) and "Board" in table_columns.get("Education", []):
                        education_data["Board"] = row["10board"]
                    
                    # 构建SQL
                    fields = list(education_data.keys())
                    values = list(education_data.values())
                    fields_str = ", ".join(fields)
                    placeholders = ", ".join(["?"] * len(fields))
                    
                    # 使用REPLACE而不是INSERT OR IGNORE，确保每个学生每个级别只有一条记录
                    conn.execute(
                        f"REPLACE INTO Education ({fields_str}) VALUES ({placeholders})",
                        tuple(values)
                    )
                    
                    # 标记为已处理
                    processed_education_levels.add("10th")
                
                # 处理12th级别的教育数据
                if has_12th_data and ("12percentage" in row and not pd.isna(row["12percentage"]) or "12board" in row and not pd.isna(row["12board"]) or "12graduation" in row and not pd.isna(row["12graduation"])):
                    education_data = {"StudentID": student_id, "EducationLevel": "12th"}
                    
                    if "12percentage" in row and not pd.isna(row["12percentage"]):
                        education_data["Percentage"] = float(row["12percentage"])
                    
                    if "12board" in row and not pd.isna(row["12board"]) and "Board" in table_columns.get("Education", []):
                        education_data["Board"] = row["12board"]
                    
                    if "12graduation" in row and not pd.isna(row["12graduation"]):
                        education_data["GraduationYear"] = int(row["12graduation"])
                    
                    # 构建SQL
                    fields = list(education_data.keys())
                    values = list(education_data.values())
                    fields_str = ", ".join(fields)
                    placeholders = ", ".join(["?"] * len(fields))
                    
                    # 使用REPLACE而不是INSERT OR IGNORE，确保每个学生每个级别只有一条记录
                    conn.execute(
                        f"REPLACE INTO Education ({fields_str}) VALUES ({placeholders})",
                        tuple(values)
                    )
                    
                    # 标记为已处理
                    processed_education_levels.add("12th")
                
                # 处理常规教育数据
                if "EducationLevel" in row and not pd.isna(row["EducationLevel"]):
                    education_level = row["EducationLevel"]
                    
                    # 如果这个级别还没处理过，才处理
                    if education_level not in processed_education_levels:
                        education_data = {"StudentID": student_id, "EducationLevel": education_level}
                        
                        if "Percentage" in row and not pd.isna(row["Percentage"]):
                            education_data["Percentage"] = float(row["Percentage"])
                        
                        if "Board" in row and not pd.isna(row["Board"]) and "Board" in table_columns.get("Education", []):
                            education_data["Board"] = row["Board"]
                        
                        if "GraduationYear" in row and not pd.isna(row["GraduationYear"]):
                            education_data["GraduationYear"] = int(row["GraduationYear"])
                        
                        # 构建SQL
                        fields = list(education_data.keys())
                        values = list(education_data.values())
                        fields_str = ", ".join(fields)
                        placeholders = ", ".join(["?"] * len(fields))
                        
                        # 使用REPLACE而不是INSERT OR IGNORE，确保每个学生每个级别只有一条记录
                        conn.execute(
                            f"REPLACE INTO Education ({fields_str}) VALUES ({placeholders})",
                            tuple(values)
                        )
            
            # 处理Cities表 - 从多个来源提取城市信息
            if "Cities" in all_tables:
                city_name = None
                
                # 尝试从多个来源获取城市名称
                if "CityName" in row and not pd.isna(row["CityName"]):
                    city_name = row["CityName"]
                elif "City" in row and not pd.isna(row["City"]):
                    city_name = row["City"]
                elif "CollegeLocation" in row and not pd.isna(row["CollegeLocation"]):
                    city_name = row["CollegeLocation"]
                elif "Location" in row and not pd.isna(row["Location"]):
                    city_name = row["Location"]
                elif "12board" in row and not pd.isna(row["12board"]):
                    # 尝试从12board提取城市信息（如果包含城市名）
                    board_parts = row["12board"].split(',')
                    if len(board_parts) > 1:
                        city_name = board_parts[-1].strip()
                
                # 如果找到城市名，插入Cities表
                if city_name:
                    # 检查是否已存在
                    cursor.execute("SELECT COUNT(*) FROM Cities WHERE CityName = ?", (city_name,))
                    if cursor.fetchone()[0] == 0:
                        # 准备城市数据
                        city_data = {"CityName": city_name}
                        
                        # 添加州和国家（如果有）
                        if "State" in row and not pd.isna(row["State"]):
                            city_data["State"] = row["State"]
                        
                        if "Country" in row and not pd.isna(row["Country"]):
                            city_data["Country"] = row["Country"]
                        
                        # 构建SQL
                        fields = list(city_data.keys())
                        values = list(city_data.values())
                        fields_str = ", ".join(fields)
                        placeholders = ", ".join(["?"] * len(fields))
                        
                        conn.execute(
                            f"INSERT OR IGNORE INTO Cities ({fields_str}) VALUES ({placeholders})",
                            tuple(values)
                        )
            
            # 处理Colleges表 - 确保每个学生都有关联的学院
            if "CollegeID" in row and not pd.isna(row["CollegeID"]) and "Colleges" in all_tables:
                college_id = int(row["CollegeID"])
                
                # 检查是否已存在
                cursor.execute("SELECT COUNT(*) FROM Colleges WHERE CollegeID = ?", (college_id,))
                if cursor.fetchone()[0] == 0:
                    # 准备学院数据
                    college_data = {"CollegeID": college_id}
                    
                    # 添加学院名称（如果有）
                    if "CollegeName" in row and not pd.isna(row["CollegeName"]):
                        college_data["CollegeName"] = row["CollegeName"]
                    else:
                        college_data["CollegeName"] = f"College {college_id}"  # 默认名称
                    
                    # 添加学院等级（如果有且表中有该列）
                    if "CollegeTier" in row and not pd.isna(row["CollegeTier"]) and "CollegeTier" in table_columns.get("Colleges", []):
                        college_data["CollegeTier"] = int(row["CollegeTier"])
                    
                    # 添加学院位置（如果有）
                    if "CollegeLocation" in row and not pd.isna(row["CollegeLocation"]) and "Location" in table_columns.get("Colleges", []):
                        college_data["Location"] = row["CollegeLocation"]
                    elif city_name and "Location" in table_columns.get("Colleges", []):
                        college_data["Location"] = city_name
                    
                    # 构建SQL
                    fields = list(college_data.keys())
                    values = list(college_data.values())
                    fields_str = ", ".join(fields)
                    placeholders = ", ".join(["?"] * len(fields))
                    
                    conn.execute(
                        f"INSERT OR IGNORE INTO Colleges ({fields_str}) VALUES ({placeholders})",
                        tuple(values)
                    )
            
            # 处理CollegePerformance表 - 如果有相关数据
            if "CollegeID" in row and not pd.isna(row["CollegeID"]) and "CollegePerformance" in all_tables:
                college_id = int(row["CollegeID"])
                
                # 检查是否有性能数据
                has_performance_data = False
                performance_data = {"CollegeID": college_id}
                
                # 收集性能数据 - 不再处理Year字段
                if "Ranking" in row and not pd.isna(row["Ranking"]) and "Ranking" in table_columns.get("CollegePerformance", []):
                    performance_data["Ranking"] = float(row["Ranking"])
                    has_performance_data = True
                
                if "PlacementPercentage" in row and not pd.isna(row["PlacementPercentage"]) and "PlacementPercentage" in table_columns.get("CollegePerformance", []):
                    performance_data["PlacementPercentage"] = float(row["PlacementPercentage"])
                    has_performance_data = True
                
                if "AverageSalary" in row and not pd.isna(row["AverageSalary"]) and "AverageSalary" in table_columns.get("CollegePerformance", []):
                    performance_data["AverageSalary"] = float(row["AverageSalary"])
                    has_performance_data = True
                elif "Salary" in row and not pd.isna(row["Salary"]) and "AverageSalary" in table_columns.get("CollegePerformance", []):
                    # 使用学生薪资作为平均薪资的估计
                    performance_data["AverageSalary"] = float(row["Salary"])
                    has_performance_data = True
                
                # 如果有性能数据，插入表
                if has_performance_data:
                    # 构建SQL
                    fields = list(performance_data.keys())
                    values = list(performance_data.values())
                    fields_str = ", ".join(fields)
                    placeholders = ", ".join(["?"] * len(fields))
                    
                    conn.execute(
                        f"INSERT OR IGNORE INTO CollegePerformance ({fields_str}) VALUES ({placeholders})",
                        tuple(values)
                    )
            
            # 处理PersonalityTraits表 - 如果有相关数据或创建默认数据
            if "PersonalityTraits" in all_tables and student_id:
                # 检查是否已存在
                cursor.execute("SELECT COUNT(*) FROM PersonalityTraits WHERE StudentID = ?", (student_id,))
                if cursor.fetchone()[0] == 0:
                    # 准备性格特征数据
                    traits_data = {"StudentID": student_id}
                    
                    # 收集性格特征数据
                    personality_traits = ["Openness", "Conscientiousness", "Extraversion", "Agreeableness", "Neuroticism"]
                    has_traits_data = False
                    
                    for trait in personality_traits:
                        if trait in row and not pd.isna(row[trait]) and trait in table_columns.get("PersonalityTraits", []):
                            traits_data[trait] = float(row[trait])
                            has_traits_data = True
                    
                    # 如果没有性格特征数据，但表中有这些列，创建随机数据
                    if not has_traits_data:
                        import random
                        for trait in personality_traits:
                            if trait in table_columns.get("PersonalityTraits", []):
                                traits_data[trait] = round(random.uniform(3.0, 8.0), 1)  # 生成3.0到8.0之间的随机数
                                has_traits_data = True
                    
                    # 如果有性格特征数据，插入表
                    if has_traits_data:
                        # 构建SQL
                        fields = list(traits_data.keys())
                        values = list(traits_data.values())
                        fields_str = ", ".join(fields)
                        placeholders = ", ".join(["?"] * len(fields))
                        
                        conn.execute(
                            f"INSERT OR IGNORE INTO PersonalityTraits ({fields_str}) VALUES ({placeholders})",
                            tuple(values)
                        )
            
            # 处理Boards表 - 只有当Boards表存在时
            if "Boards" in all_tables:
                # 处理10board
                if "10board" in row and not pd.isna(row["10board"]):
                    # 检查是否已存在
                    cursor.execute("SELECT COUNT(*) FROM Boards WHERE BoardName = ?", (row["10board"],))
                    if cursor.fetchone()[0] == 0:
                        conn.execute("INSERT INTO Boards (BoardName) VALUES (?)", (row["10board"],))
                
                # 处理12board
                if "12board" in row and not pd.isna(row["12board"]):
                    # 检查是否已存在
                    cursor.execute("SELECT COUNT(*) FROM Boards WHERE BoardName = ?", (row["12board"],))
                    if cursor.fetchone()[0] == 0:
                        conn.execute("INSERT INTO Boards (BoardName) VALUES (?)", (row["12board"],))
            
            # 为每个表插入数据
            for table_name, table_data in row_tables_data.items():
                # 跳过Education表，因为我们已经单独处理了
                if table_name == "Education":
                    continue
                
                if table_name in all_tables and table_data["columns"] and table_data["values"]:
                    # 确保表中有主键
                    if table_name == "Students" and "StudentID" not in table_data["columns"]:
                        table_data["columns"].append("StudentID")
                        table_data["values"].append(student_id)
                    elif table_name in ["Skills", "Salaries", "PersonalityTraits"] and "StudentID" not in table_data["columns"]:
                        table_data["columns"].append("StudentID")
                        table_data["values"].append(student_id)
                    
                    # 检查列是否存在于表中
                    valid_columns = []
                    valid_values = []
                    for i, col in enumerate(table_data["columns"]):
                        if col in table_columns.get(table_name, []):
                            valid_columns.append(col)
                            valid_values.append(table_data["values"][i])
                    
                    if valid_columns:
                        # 构建SQL
                        fields_str = ", ".join(valid_columns)
                        placeholders = ", ".join(["?"] * len(valid_columns))
                        
                        # 执行插入
                        try:
                            conn.execute(
                                f"INSERT OR IGNORE INTO {table_name} ({fields_str}) VALUES ({placeholders})",
                                tuple(valid_values)
                            )
                        except Exception as e:
                            st.warning(f"插入{table_name}表时出错: {str(e)}")
            
        except Exception as e:
            st.warning(f"处理行 {row.get('StudentID', '未知')} 时出错: {str(e)}")
            continue
    
    # 提交所有更改
    conn.commit()
    
    # 返回导入的表和记录数
    cursor = conn.cursor()
    import_stats = {}
    
    for table in all_tables:
        cursor.execute(f"SELECT COUNT(*) FROM {table}")
        count = cursor.fetchone()[0]
        import_stats[table] = count
    
    return import_stats

# 初始化页面配置
st.set_page_config(page_title="工科毕业生薪酬预测自动化管理系统", layout="wide")

# 侧边栏导航
with st.sidebar:
    st.header("导航菜单")
    st.title("工科毕业生薪酬预测自动化管理系统")
    page = st.radio("功能选择", ["CSV导入","数据管理",  "薪资预测", "报告模板管理", "使用手册"])

# 数据管理模块
if page == "数据管理":
    st.title("📊 工科毕业生数据管理")
    tab1, tab2, tab3, tab4, tab5 = st.tabs(["新增记录", "修改记录", "删除记录", "综合查询", "数据初始化"])

    with tab1:  # 新增记录
        st.subheader("添加新学生记录")
        
        # 创建表单
        with st.form("add_student_form"):
            st.subheader("添加学生信息")
            student_id = st.number_input("学号", min_value=1000)
            gender = st.selectbox("性别", ["M", "F"])
            dob = st.date_input("出生日期", max_value=date.today())
            
            # 添加教育信息字段
            education_level = st.selectbox("教育水平", ["10th", "12th", "Graduate", "Masters", "PhD"])
            percentage = st.slider("成绩百分比", 0.0, 100.0, 75.0)
            board = st.text_input("教育委员会")
            
            # 添加薪资信息
            salary = st.number_input("薪资", min_value=0, value=50000)
            
            if st.form_submit_button("提交"):
                try:
                    with connect_db() as conn:
                        # 检查Education表结构
                        cursor = conn.cursor()
                        cursor.execute("PRAGMA table_info(Education)")
                        education_columns = [row[1] for row in cursor.fetchall()]
                        
                        # 开启事务
                        with conn:
                            # 插入学生基本信息
                            conn.execute("INSERT INTO Students VALUES (?,?,?)", 
                                    (student_id, gender, dob))
                            
                            # 插入教育信息 - 根据表结构动态构建SQL
                            if "Board" in education_columns:
                                conn.execute(
                                    "INSERT INTO Education (StudentID, EducationLevel, Percentage, Board) VALUES (?,?,?,?)",
                                    (student_id, education_level, percentage, board)
                                )
                            else:
                                conn.execute(
                                    "INSERT INTO Education (StudentID, EducationLevel, Percentage) VALUES (?,?,?)",
                                    (student_id, education_level, percentage)
                                )
                            
                            # 插入薪资信息
                            conn.execute(
                                "INSERT INTO Salaries (StudentID, Salary) VALUES (?,?)",
                                (student_id, salary)
                            )
                            
                    st.success("学生记录添加成功")
                except sqlite3.IntegrityError:
                    st.error("错误：学号已存在")
                except Exception as e:
                    st.error(f"添加失败：{str(e)}")

    with tab2:  # 修改记录
        st.subheader("修改学生信息")
        
        # 获取所有学生ID
        with connect_db() as conn:
            student_ids = pd.read_sql("SELECT StudentID FROM Students ORDER BY StudentID", conn)
        
        if not student_ids.empty:
            selected_id = st.selectbox("选择学生", student_ids["StudentID"])
            
            # 检查数据库表结构
            with connect_db() as conn:
                cursor = conn.cursor()
                
                # 获取Students表的列
                cursor.execute("PRAGMA table_info(Students)")
                students_columns = [row[1] for row in cursor.fetchall()]
                
                # 检查Education表是否存在
                cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='Education'")
                education_exists = cursor.fetchone() is not None
                
                # 检查Salaries表是否存在
                cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='Salaries'")
                salaries_exists = cursor.fetchone() is not None
                
                # 获取Education表的列（如果存在）
                education_columns = []
                if education_exists:
                    cursor.execute("PRAGMA table_info(Education)")
                    education_columns = [row[1] for row in cursor.fetchall()]
                
                # 获取Salaries表的列（如果存在）
                salary_columns = []
                if salaries_exists:
                    cursor.execute("PRAGMA table_info(Salaries)")
                    salary_columns = [row[1] for row in cursor.fetchall()]
            
            # 构建动态查询
            query_parts = ["SELECT s.*"]
            joins = []
            
            if education_exists:
                edu_cols = [f"e.{col}" for col in education_columns if col != "StudentID"]
                if edu_cols:
                    query_parts.append(", " + ", ".join(edu_cols))
                joins.append("LEFT JOIN Education e ON s.StudentID = e.StudentID")
            
            if salaries_exists and "Salary" in salary_columns:
                query_parts.append(", sa.Salary")
                joins.append("LEFT JOIN Salaries sa ON s.StudentID = sa.StudentID")
            
            query = " ".join(query_parts) + " FROM Students s " + " ".join(joins) + f" WHERE s.StudentID = {selected_id}"
            
            # 获取选中学生的信息
            with connect_db() as conn:
                try:
                    student_data = pd.read_sql(query, conn)
                    
                    if not student_data.empty:
                        with st.form("修改表单"):
                            # 基本信息
                            gender = st.selectbox("性别", ["M", "F"], 
                                                index=0 if student_data.iloc[0]["Gender"] == "M" else 1)
                            
                            # 日期处理
                            try:
                                dob_value = pd.to_datetime(student_data.iloc[0]["DOB"]).date()
                            except:
                                dob_value = date.today()
                            
                            dob = st.date_input("出生日期", value=dob_value, max_value=date.today())
                            
                            # 教育信息
                            edu_levels = ["10th", "12th", "Graduate", "Masters", "PhD"]
                            current_level = student_data.iloc[0].get("EducationLevel", edu_levels[0])
                            level_index = edu_levels.index(current_level) if current_level in edu_levels else 0
                            
                            education_level = st.selectbox("教育水平", edu_levels, index=level_index)

                            # 修复percentage的处理
                            percentage_value = student_data.iloc[0].get("Percentage")
                            if percentage_value is not None:
                                try:
                                    percentage_value = float(percentage_value)
                                except (ValueError, TypeError):
                                    percentage_value = 75.0
                            else:
                                percentage_value = 75.0

                            percentage = st.slider("成绩百分比", 0.0, 100.0, percentage_value)

                            board = st.text_input("教育委员会", value=student_data.iloc[0].get("Board", ""))
                            
                            # 修复salary的处理
                            salary_value = student_data.iloc[0].get("Salary")
                            if salary_value is not None:
                                try:
                                    salary_value = int(float(salary_value))
                                except (ValueError, TypeError):
                                    salary_value = 50000
                            else:
                                salary_value = 50000

                            salary = st.number_input("薪资", min_value=0, value=salary_value)
                            
                            # 修复修改记录功能中的错误
                            if st.form_submit_button("更新记录"):
                                try:
                                    with connect_db() as conn:
                                        # 检查Education表结构
                                        cursor = conn.cursor()
                                        cursor.execute("PRAGMA table_info(Education)")
                                        education_columns = [row[1] for row in cursor.fetchall()]
                                        
                                        # 开启事务
                                        with conn:
                                            # 更新学生基本信息
                                            conn.execute("""
                                                UPDATE Students 
                                                SET Gender = ?, DOB = ?
                                                WHERE StudentID = ?
                                            """, (gender, dob, selected_id))
                                            
                                            # 更新教育信息 - 根据表结构动态构建SQL
                                            if "Board" in education_columns:
                                                conn.execute("""
                                                    UPDATE Education 
                                                    SET EducationLevel = ?, Percentage = ?, Board = ?
                                                    WHERE StudentID = ?
                                                """, (education_level, percentage, board, selected_id))
                                            else:
                                                conn.execute("""
                                                    UPDATE Education 
                                                    SET EducationLevel = ?, Percentage = ?
                                                    WHERE StudentID = ?
                                                """, (education_level, percentage, selected_id))
                                            
                                            # 更新薪资信息
                                            conn.execute("""
                                                UPDATE Salaries 
                                                SET Salary = ?
                                                WHERE StudentID = ?
                                            """, (salary, selected_id))
                                            
                                    st.success("学生记录更新成功")
                                except Exception as e:
                                    st.error(f"更新失败：{str(e)}")
                    else:
                        st.warning("无法获取学生信息")
                except Exception as e:
                    st.error(f"查询失败: {str(e)}")
                    st.code(query)  # 显示查询语句以便调试
        else:
            st.warning("数据库中没有学生记录")

    with tab3:  # 删除记录
        st.subheader("删除学生记录")
        
        # 获取所有学生ID
        with connect_db() as conn:
            student_ids = pd.read_sql("SELECT StudentID, Gender, DOB FROM Students ORDER BY StudentID", conn)
        
        if not student_ids.empty:
            st.dataframe(student_ids, use_container_width=True)
            
            with st.form("删除表单"):
                selected_id = st.number_input("输入要删除的学生ID", min_value=1000)
                confirm = st.checkbox("我确认要删除此学生的所有相关记录")
                
                if st.form_submit_button("删除记录"):
                    if confirm:
                        try:
                            with connect_db() as conn:
                                # 开启事务
                                with conn:
                                    # 删除相关表中的记录
                                    conn.execute("DELETE FROM Salaries WHERE StudentID = ?", (selected_id,))
                                    conn.execute("DELETE FROM Education WHERE StudentID = ?", (selected_id,))
                                    conn.execute("DELETE FROM Students WHERE StudentID = ?", (selected_id,))
                                    
                            st.success(f"学生ID {selected_id} 的所有记录已删除")
                            st.experimental_rerun()
                        except Exception as e:
                            st.error(f"删除失败：{str(e)}")
                    else:
                        st.warning("请确认删除操作")
        else:
            st.warning("数据库中没有学生记录")

    with tab4:  # 综合查询
        st.subheader("多表联合查询")
        
        # 添加一个简单模式选项
        query_mode = st.radio("查询模式", ["标准查询", "查看所有记录"], horizontal=True)
        
        if query_mode == "查看所有记录":
            # 直接查询所有学生记录
            with connect_db() as conn:
                try:
                    # 获取表信息
                    cursor = conn.cursor()
                    cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
                    tables = [row[0] for row in cursor.fetchall()]
                    
                    # 简单查询所有学生
                    all_students = pd.read_sql("SELECT * FROM Students", conn)
                    
                    if not all_students.empty:
                        st.write(f"#### 所有学生记录 ({len(all_students)} 条)")
                        st.dataframe(all_students, use_container_width=True)
                        
                        # 显示表的记录数
                        st.write("#### 数据库表统计")
                        stats = []
                        for table in tables:
                            count = pd.read_sql(f"SELECT COUNT(*) as count FROM {table}", conn).iloc[0]['count']
                            stats.append({"表名": table, "记录数": count})
                        
                        st.dataframe(pd.DataFrame(stats), use_container_width=True)
                        
                        # 下载按钮
                        st.download_button(
                            "下载所有学生记录", 
                            all_students.to_csv(index=False).encode('utf-8'),
                            "all_students.csv",
                            "text/csv",
                            key='download-all'
                        )
                    else:
                        st.info("数据库中没有学生记录")
                except Exception as e:
                    st.error(f"查询失败: {str(e)}")
        else:
            # 检查数据库表结构
            with connect_db() as conn:
                cursor = conn.cursor()
                
                # 获取表信息
                cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
                tables = [row[0] for row in cursor.fetchall()]
                
                # 获取各表的列信息
                table_columns = {}
                for table in tables:
                    cursor.execute(f"PRAGMA table_info({table})")
                    table_columns[table] = [row[1] for row in cursor.fetchall()]
            
            # 创建查询构建器
            st.write("#### 查询条件设置")
            
            # 使用多列布局
            filter_col1, filter_col2 = st.columns(2)
            
            with filter_col1:
                # 学号查询 - 新增
                student_id_input = st.text_input("学号查询", placeholder="输入学号，留空查询所有")
                
                # 基本筛选条件
                gender_filter = st.multiselect("性别", ["M", "F"], default=["M", "F"])
                
                # 修改日期范围筛选，添加一个选项来启用/禁用日期筛选
                use_date_filter = st.checkbox("启用日期筛选", value=False)
                if use_date_filter:
                    date_col1, date_col2 = st.columns(2)
                    with date_col1:
                        start_date = st.date_input("出生日期从", value=date(1980, 1, 1))
                    with date_col2:
                        end_date = st.date_input("到", value=date.today())
            
            with filter_col2:
                # 教育和薪资筛选
                if "Education" in tables and "Percentage" in table_columns.get("Education", []):
                    min_percentage = st.slider("最低成绩百分比", 0.0, 100.0, 0.0)
                
                if "Salaries" in tables and "Salary" in table_columns.get("Salaries", []):
                    min_salary = st.slider("最低薪资", 0, 100000, 0)
            
            # 高级查询选项
            with st.expander("高级查询选项"):
                # 选择要显示的列
                st.write("选择要显示的列：")
                
                # 学生基本信息列
                st_cols = []
                if "Students" in tables:
                    st_cols = [f"s.{col}" for col in table_columns.get("Students", [])]
                    selected_st_cols = st.multiselect(
                        "学生基本信息", 
                        table_columns.get("Students", []),
                        default=["StudentID", "Gender", "DOB"]
                    )
                
                # 教育信息列
                edu_cols = []
                if "Education" in tables:
                    edu_cols = [f"e.{col}" for col in table_columns.get("Education", []) if col != "StudentID"]
                    selected_edu_cols = st.multiselect(
                        "教育信息", 
                        [col for col in table_columns.get("Education", []) if col != "StudentID"],
                        default=["EducationLevel", "Percentage"] if all(col in table_columns.get("Education", []) for col in ["EducationLevel", "Percentage"]) else []
                    )
                
                # 薪资信息列
                sal_cols = []
                if "Salaries" in tables:
                    sal_cols = [f"sa.{col}" for col in table_columns.get("Salaries", []) if col != "StudentID"]
                    selected_sal_cols = st.multiselect(
                        "薪资信息", 
                        [col for col in table_columns.get("Salaries", []) if col != "StudentID"],
                        default=["Salary"] if "Salary" in table_columns.get("Salaries", []) else []
                    )
                
                # 排序选项
                all_columns = []
                if selected_st_cols:
                    all_columns.extend([f"s.{col}" for col in selected_st_cols])
                if selected_edu_cols:
                    all_columns.extend([f"e.{col}" for col in selected_edu_cols])
                if selected_sal_cols:
                    all_columns.extend([f"sa.{col}" for col in selected_sal_cols])
                
                if all_columns:
                    sort_options = [col.split(".")[-1] for col in all_columns]
                    sort_by = st.selectbox("排序依据", ["StudentID"] + sort_options)
                    sort_order = st.radio("排序方式", ["升序", "降序"], horizontal=True)
            
            # 构建查询
            # 选择要显示的列
            select_columns = []
            
            # 添加学生表的列
            if "Students" in tables:
                if selected_st_cols:
                    select_columns.extend([f"s.{col}" for col in selected_st_cols])
                else:
                    select_columns.extend([f"s.{col}" for col in table_columns.get("Students", [])[:3]])  # 默认前3列
            
            # 添加Education表的列
            if "Education" in tables:
                if selected_edu_cols:
                    select_columns.extend([f"e.{col}" for col in selected_edu_cols])
            
            # 添加Salaries表的列
            if "Salaries" in tables:
                if selected_sal_cols:
                    select_columns.extend([f"sa.{col}" for col in selected_sal_cols])
            
            # 构建基本查询
            query = f"""
            SELECT {', '.join(select_columns)}
        FROM Students s
            """
            
            # 添加JOIN语句
            joins = []
            if "Education" in tables and (selected_edu_cols or "Education" in tables and "Percentage" in table_columns.get("Education", [])):
                joins.append("LEFT JOIN Education e ON s.StudentID = e.StudentID")
            
            if "Salaries" in tables and (selected_sal_cols or "Salaries" in tables and "Salary" in table_columns.get("Salaries", [])):
                joins.append("LEFT JOIN Salaries sa ON s.StudentID = sa.StudentID")
            
            query += " " + " ".join(joins)
            
            # 添加WHERE条件
            conditions = []
            params = []
            
            # 学号筛选
            if student_id_input and student_id_input.strip():
                try:
                    student_id = int(student_id_input.strip())
                    conditions.append("s.StudentID = ?")
                    params.append(student_id)
                except ValueError:
                    st.warning("请输入有效的学号")
            
            # 性别筛选 - 只有在选择了性别时才添加条件
            if gender_filter and len(gender_filter) < 2:  # 如果选择了所有性别，就不需要添加条件
                conditions.append(f"s.Gender IN ({','.join(['?']*len(gender_filter))})")
                params.extend(gender_filter)
            
            # 日期范围筛选 - 只有在启用时才添加
            if 'use_date_filter' in locals() and use_date_filter:
                conditions.append("s.DOB BETWEEN ? AND ?")
                params.extend([start_date, end_date])
            
            # 成绩百分比筛选 - 修改条件，确保不会过滤掉NULL值
            if "Education" in tables and "Percentage" in table_columns.get("Education", []) and min_percentage > 0:
                conditions.append("(e.Percentage >= ? OR e.Percentage IS NULL)")
                params.append(min_percentage)
            
            # 薪资筛选 - 修改条件，确保不会过滤掉NULL值
            if "Salaries" in tables and "Salary" in table_columns.get("Salaries", []) and min_salary > 0:
                conditions.append("(sa.Salary >= ? OR sa.Salary IS NULL)")
                params.append(min_salary)
            
            if conditions:
                query += " WHERE " + " AND ".join(conditions)
            
            # 添加排序
            if 'sort_by' in locals():
                table_prefix = ""
                if sort_by in table_columns.get("Students", []):
                    table_prefix = "s."
                elif sort_by in table_columns.get("Education", []):
                    table_prefix = "e."
                elif sort_by in table_columns.get("Salaries", []):
                    table_prefix = "sa."
                
                query += f" ORDER BY {table_prefix}{sort_by} {'DESC' if sort_order == '降序' else 'ASC'}"
            else:
                query += " ORDER BY s.StudentID"
            
            # 执行查询按钮
            if st.button("执行查询", use_container_width=True):
                with st.spinner("查询中..."):
                    try:
                        with connect_db() as conn:
                            results = pd.read_sql_query(query, conn, params=params)
                        
                        # 显示结果
                        if not results.empty:
                            st.write(f"#### 查询结果 ({len(results)} 条记录)")
                            st.dataframe(results, use_container_width=True)
                            
                            # 数据可视化
                            if len(results) > 1:
                                st.write("#### 数据可视化")
                                viz_col1, viz_col2 = st.columns(2)
                                
                                with viz_col1:
                                    # 性别分布
                                    if "Gender" in results.columns:
                                        gender_counts = results["Gender"].value_counts().reset_index()
                                        gender_counts.columns = ["性别", "人数"]
                                        fig1 = px.pie(gender_counts, values="人数", names="性别", title="性别分布")
                                        st.plotly_chart(fig1, use_container_width=True)
                                
                                with viz_col2:
                                    # 成绩或薪资分布
                                    if "Percentage" in results.columns:
                                        fig2 = px.histogram(results, x="Percentage", title="成绩分布")
                                        st.plotly_chart(fig2, use_container_width=True)
                                    elif "Salary" in results.columns:
                                        fig2 = px.histogram(results, x="Salary", title="薪资分布")
                                        st.plotly_chart(fig2, use_container_width=True)
                            
                            # 下载按钮
                            st.download_button(
                                "下载查询结果", 
                                results.to_csv(index=False).encode('utf-8'),
                                "student_query_results.csv",
                                "text/csv",
                                key='download-csv'
                            )
                        else:
                            st.info("没有符合条件的记录")
                    except Exception as e:
                        st.error(f"查询失败: {str(e)}")

    # 添加数据初始化标签页
    with tab5:  # 数据初始化
        st.subheader("数据库初始化")
        
        # 获取数据库表信息
        with connect_db() as conn:
            cursor = conn.cursor()
            cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
            tables = [row[0] for row in cursor.fetchall()]
            
            # 获取各表的记录数
            table_stats = []
            for table in tables:
                count = pd.read_sql(f"SELECT COUNT(*) as count FROM {table}", conn).iloc[0]['count']
                table_stats.append({"表名": table, "记录数": count})
        
        # 显示当前数据库状态
        st.write("#### 当前数据库状态")
        st.dataframe(pd.DataFrame(table_stats), use_container_width=True)
        
        # 数据初始化选项
        st.write("#### 初始化选项")
        
        # 创建一个表单，要求确认
        with st.form("初始化表单"):
            st.warning("⚠️ 警告：此操作将清空所有表中的数据，但保留表结构。此操作不可撤销！")
            
            # 选择要清空的表
            tables_to_clear = st.multiselect(
                "选择要清空的表",
                tables,
                default=tables
            )
            
            # 要求输入确认文本
            confirmation = st.text_input(
                "请输入 'CONFIRM' 以确认清空操作",
                placeholder="CONFIRM"
            )
            
            # 提交按钮
            submitted = st.form_submit_button("执行初始化", use_container_width=True)
            
            if submitted:
                if confirmation == "CONFIRM" and tables_to_clear:
                    try:
                        with connect_db() as conn:
                            # 开启事务
                            with conn:
                                for table in tables_to_clear:
                                    conn.execute(f"DELETE FROM {table}")
                            
                        st.success(f"已成功清空以下表: {', '.join(tables_to_clear)}")
                        st.experimental_rerun()
                    except Exception as e:
                        st.error(f"初始化失败: {str(e)}")
                else:
                    if not confirmation == "CONFIRM":
                        st.error("确认文本不正确，操作已取消")
                    if not tables_to_clear:
                        st.error("未选择任何表，操作已取消")

# CSV导入模块
elif page == "CSV导入":
    st.title("📁 工科毕业生数据导入处理中心")
    
    uploaded_file = st.file_uploader("选择CSV文件", type=['csv', 'xlsx'])
    
    if uploaded_file is not None:
        try:
            # 确定文件类型
            file_type = uploaded_file.name.split('.')[-1]
            
            if file_type == 'csv':
                df = pd.read_csv(uploaded_file)
            elif file_type in ['xlsx', 'xls']:
                df = pd.read_excel(uploaded_file)
            
            # 列名映射 - 处理可能的列名差异
            column_mapping = {
                'ID': 'StudentID',
                'id': 'StudentID',
                'student_id': 'StudentID',
                'studentid': 'StudentID',
                'gender': 'Gender',
                'sex': 'Gender',
                'dob': 'DOB',
                'date_of_birth': 'DOB',
                'birth_date': 'DOB',
                'percentage': 'Percentage',
                'score': 'Percentage',
                'board': 'Board',
                'school': 'Board'
            }
            
            # 应用列名映射
            df.rename(columns=column_mapping, inplace=True)
            
            # 显示原始数据预览
            st.subheader("原始数据预览")
            st.dataframe(df.head(), use_container_width=True)
            
            # 保存原始数据到session_state
            st.session_state.raw_df = df
            
            # 显示数据处理选项
            with st.expander("数据处理选项", expanded=True):
                # 定义处理步骤
                processing_steps = [
                    "数据清洗 - 处理空值和异常值",
                    "字段标准化 - 统一格式和单位",
                    "日期字段转换 - 标准化日期格式",
                    "唯一性校验 - 检查重复记录",
                    "数值类型转换 - 确保数据类型正确"
                ]
                
                # 显示处理步骤
                st.write("将执行以下处理步骤:")
                for step in processing_steps:
                    st.write(f"- {step}")
                
                # 处理按钮
                if st.button("执行数据处理", use_container_width=True):
                    try:
                        # 创建处理进度条
                        progress_bar = st.progress(0)
                        status_text = st.empty()
                        
                        # 执行数据处理流程
                        for i, step in enumerate(processing_steps):
                            # 更新进度
                            progress = (i + 1) / len(processing_steps)
                            progress_bar.progress(progress)
                            status_text.text(f"正在执行: {step}")
                            time.sleep(0.5)  # 模拟处理时间
                        
                        # 执行实际的数据处理
                        processed_df = data_processing_pipeline(st.session_state.raw_df)
                        
                        # 显示处理后的数据预览
                        st.subheader("处理后的数据预览")
                        st.dataframe(processed_df.head(), use_container_width=True)
                        
                        # 保存处理后的数据到session_state
                        st.session_state.processed_df = processed_df
                        
                        # 显示处理结果统计
                        st.subheader("处理结果统计")
                        st.write(f"总行数: {len(processed_df)}")
                        st.write(f"总列数: {processed_df.shape[1]}")
                        
                        # 显示空值统计
                        st.write("空值统计:")
                        st.write(processed_df.isna().sum())
                        
                        st.success("数据处理成功完成!")
                    except Exception as e:
                        st.error(f"数据处理失败: {str(e)}")
            
            # 数据库写入表单
            if 'processed_df' in st.session_state:
                with st.form("database_import"):
                    st.subheader("数据库导入选项")
                    
                    # 导入模式选择
                    import_mode = st.radio(
                        "选择导入模式",
                        ["追加模式", "覆盖模式"],
                        help="追加模式：保留现有数据\n覆盖模式：清空后导入"
                    )
                    
                    # 添加导入到数据库的按钮
                    submit_button = st.form_submit_button("💾 确认写入数据库", use_container_width=True)
                    
                    if submit_button:
                        try:
                            with connect_db() as conn:
                                # 开启事务
                                with conn:
                                    save_to_database(
                                        conn,
                                        st.session_state.processed_df,
                                        mode="replace" if import_mode == "覆盖模式" else "append"
                                    )
                            st.success("数据成功持久化存储!")
                            st.balloons()
                        except Exception as e:
                            st.error(f"数据库写入失败: {str(e)}")
            else:
                st.info("请先执行数据处理步骤")
        except Exception as e:
            st.error(f"文件读取失败: {str(e)}")
    else:
        st.info("请上传CSV文件开始处理流程")

# 薪资预测模块
elif page == "薪资预测":
    st.title("💰 工科毕业生薪资预测")
    
    # 获取报告模板列表
    with connect_db() as conn:
        try:
            templates_df = pd.read_sql("SELECT TemplateID, TemplateName, Description FROM ReportTemplates", conn)
        except:
            # 如果表不存在，创建表并添加默认模板
            conn.execute("""
                CREATE TABLE IF NOT EXISTS ReportTemplates (
                    TemplateID INTEGER PRIMARY KEY,
                    TemplateName TEXT NOT NULL,
                    Description TEXT,
                    Content TEXT NOT NULL,
                    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            """)
            # 插入默认模板
            for i, template in enumerate(default_templates, 1):
                conn.execute("""
                    INSERT INTO ReportTemplates (TemplateID, TemplateName, Description, Content)
                    VALUES (?, ?, ?, ?)
                """, (i, template["name"], template["description"], template["content"]))
            conn.commit()
            templates_df = pd.read_sql("SELECT TemplateID, TemplateName, Description FROM ReportTemplates", conn)
    
    # 创建预测表单
    with st.form("prediction_form"):
        st.subheader("输入毕业生信息")
        
        col1, col2 = st.columns(2)
    
        with col1:
            # 基本信息
            name = st.text_input("姓名", placeholder="请输入毕业生姓名")
            gender = st.selectbox("性别", ["M", "F"])
            
            # 修复日期输入错误
            # 使用固定的最小日期而不是动态计算
            min_date = date(1970, 1, 1)
            max_date = date.today() - pd.Timedelta(days=365*18)
            dob = st.date_input("出生日期", value=max_date, min_value=min_date, max_value=max_date)
            
            # 添加更多字段
            student_id = st.text_input("学号", placeholder="选填")
            phone = st.text_input("联系电话", placeholder="选填")
            email = st.text_input("电子邮箱", placeholder="选填")
    
        with col2:
            # 教育信息
            education_level = st.selectbox("教育水平", ["10th", "12th", "Graduate", "Masters", "PhD"])
            percentage = st.slider("成绩百分比", 0.0, 100.0, 75.0)
            board = st.text_input("教育委员会/学校", placeholder="如：清华大学")
            major = st.text_input("专业", placeholder="如：计算机科学")
            graduation_year = st.number_input("毕业年份", min_value=2000, max_value=date.today().year + 4, value=date.today().year)
            experience = st.number_input("实习经验(月)", min_value=0, max_value=60, value=0)
        
        # 专业技能评分
            st.subheader("专业技能评分")
            skill_col1, skill_col2, skill_col3 = st.columns(3)
        
            with skill_col1:
                english = st.slider("英语能力", 1, 10, 7)
                programming = st.slider("编程能力", 1, 10, 7)
            
            with skill_col2:
                analytical = st.slider("分析能力", 1, 10, 7)
                teamwork = st.slider("团队协作", 1, 10, 7)
            
            with skill_col3:
                communication = st.slider("沟通能力", 1, 10, 7)
                problem_solving = st.slider("解决问题能力", 1, 10, 7)
        
        # 项目经验
            st.subheader("项目经验")
            project_experience = st.text_area("描述您的项目经验", placeholder="请简要描述您参与过的项目，包括角色和贡献", height=100)
        
        # 证书和奖项
            st.subheader("证书和奖项")
            certificates = st.text_area("列出您获得的证书和奖项", placeholder="如：英语六级证书、程序设计大赛一等奖等", height=80)
        
        # 选择报告模板
            st.subheader("选择报告模板")
            if not templates_df.empty:
                template_id = st.selectbox(
                    "选择报告模板", 
                    templates_df["TemplateID"].tolist(),
                    format_func=lambda x: f"{templates_df[templates_df['TemplateID']==x]['TemplateName'].iloc[0]} - {templates_df[templates_df['TemplateID']==x]['Description'].iloc[0]}"
                )
            else:
                st.warning("未找到报告模板，请先在报告模板管理中添加模板")
                template_id = None
        
        # 提交按钮
            submitted = st.form_submit_button("生成预测报告", use_container_width=True)
    
    # 处理表单提交
    if submitted and template_id is not None:
        if not name:
            st.error("请输入姓名")
        else:
            # 计算预测薪资 - 使用更复杂的模型
            base_salary = 50000
            
            # 教育水平加成
            education_bonus = {
                "10th": 0,
                "12th": 5000,
                "Graduate": 15000,
                "Masters": 25000,
                "PhD": 40000
            }
            
            # 成绩加成
            percentage_bonus = percentage * 100
            
            # 经验加成
            experience_bonus = experience * 500
            
            # 技能加成
            skills_avg = (english + programming + analytical + teamwork + communication + problem_solving) / 6
            skills_bonus = skills_avg * 2000
            
            # 项目经验加成
            project_bonus = 0
            if project_experience:
                # 简单估算项目经验的价值
                project_words = len(project_experience.split())
                project_bonus = min(project_words * 10, 5000)  # 最高5000
            
            # 证书加成
            cert_bonus = 0
            if certificates:
                # 简单估算证书的价值
                cert_count = len(certificates.split(','))
                cert_bonus = cert_count * 1000  # 每个证书1000
            
            # 计算总薪资
            predicted_salary = base_salary + education_bonus[education_level] + percentage_bonus + experience_bonus + skills_bonus + project_bonus + cert_bonus
            
            # 获取选定的模板
            with connect_db() as conn:
                template_content = pd.read_sql(f"SELECT Content FROM ReportTemplates WHERE TemplateID = {template_id}", conn).iloc[0]["Content"]
            
            # 准备模板变量
            template_vars = {
                "name": name,
                "gender": "男" if gender == "M" else "女",
                "age": (date.today() - dob).days // 365,
                "education_level": {
                    "10th": "高中",
                    "12th": "中专/高中",
                    "Graduate": "本科",
                    "Masters": "硕士",
                    "PhD": "博士"
                }[education_level],
                "percentage": percentage,
                "experience": experience,
                "english": english,
                "programming": programming,
                "analytical": analytical,
                "teamwork": teamwork,
                "communication": communication,
                "problem_solving": problem_solving,
                "skills_avg": round(skills_avg, 1),
                "predicted_salary": f"{predicted_salary:,.2f}",
                "prediction_date": date.today().strftime("%Y年%m月%d日"),
                "market_comparison": "高于" if predicted_salary > 60000 else "接近" if predicted_salary > 40000 else "低于",
                "major": major if major else "未指定",
                "board": board if board else "未指定",
                "graduation_year": graduation_year,
                "project_experience": project_experience if project_experience else "无",
                "certificates": certificates if certificates else "无"
            }
            
            # 渲染模板
            for key, value in template_vars.items():
                template_content = template_content.replace(f"{{{{{key}}}}}", str(value))
            

            # 将 Markdown 转换为 HTML
            markdown_html = markdown.markdown(template_content)

            # 显示报告
            st.success("预测报告生成成功!")

            # 使用 expander 显示报告
            with st.expander("预测报告", expanded=True):
                st.markdown(markdown_html, unsafe_allow_html=True)

            
            # 提供下载选项
            report_html = f"""
            <html>
            <head>
                <title>薪资预测报告 - {name}</title>
                <style>
                    body {{ font-family: Arial, sans-serif; margin: 40px; }}
                    h1 {{ color: #2c3e50; }}
                    .report-container {{ border: 1px solid #ddd; padding: 20px; }}
                    .highlight {{ color: #e74c3c; font-weight: bold; }}
                </style>
            </head>
            <body>
                <div class="report-container">
                    {markdown_html}
                </div>
            </body>
            </html>
            """
            
            st.download_button(
                "下载报告 (HTML)",
                report_html,
                f"薪资预测报告_{name}_{date.today().strftime('%Y%m%d')}.html",
                "text/html"
            )
            
            # 保存预测记录到数据库（可选）
            try:
                with connect_db() as conn:
                    conn.execute("""
                        CREATE TABLE IF NOT EXISTS PredictionHistory (
                            PredictionID INTEGER PRIMARY KEY,
                            Name TEXT,
                            Gender TEXT,
                            DOB DATE,
                            EducationLevel TEXT,
                            Percentage REAL,
                            Experience INTEGER,
                            PredictedSalary REAL,
                            PredictionDate DATE,
                            TemplateID INTEGER
                        )
                    """)
                    
                    conn.execute("""
                        INSERT INTO PredictionHistory 
                        (Name, Gender, DOB, EducationLevel, Percentage, Experience, PredictedSalary, PredictionDate, TemplateID)
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
                    """, (
                        name, gender, dob, education_level, percentage, experience, 
                        predicted_salary, date.today(), template_id
                    ))
                    conn.commit()
            except Exception as e:
                st.warning(f"保存预测记录时出错: {str(e)}")

# 报告模板管理模块
elif page == "报告模板管理":
    st.title("📝 报告模板管理")
    
    # 创建模板表（如果不存在）
    with connect_db() as conn:
        conn.execute("""
            CREATE TABLE IF NOT EXISTS ReportTemplates (
                TemplateID INTEGER PRIMARY KEY,
                TemplateName TEXT NOT NULL,
                Description TEXT,
                Content TEXT NOT NULL,
                CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
    
    # 创建标签页
    template_tab1, template_tab2, template_tab3 = st.tabs(["查看模板", "添加模板", "编辑模板"])
    
    with template_tab1:  # 查看模板
        st.subheader("现有报告模板")
        
        # 获取所有模板
        with connect_db() as conn:
            templates = pd.read_sql("SELECT * FROM ReportTemplates ORDER BY TemplateID", conn)
        
        if not templates.empty:
            for _, template in templates.iterrows():
                with st.expander(f"{template['TemplateID']}. {template['TemplateName']}"):
                    st.write(f"**描述**: {template['Description']}")
                    st.write("**模板内容**:")
                    st.text_area("", template['Content'], height=200, key=f"view_{template['TemplateID']}", disabled=True)
                    st.write(f"创建时间: {template['CreatedAt']}")
                    st.write(f"更新时间: {template['UpdatedAt']}")
                    
                    # 删除按钮
                    if st.button("删除此模板", key=f"delete_{template['TemplateID']}"):
                        with connect_db() as conn:
                            conn.execute("DELETE FROM ReportTemplates WHERE TemplateID = ?", (template['TemplateID'],))
                            conn.commit()
                        st.success(f"模板 '{template['TemplateName']}' 已删除")
                        st.experimental_rerun()
        else:
            st.info("暂无模板，请添加新模板")
            
            # 添加默认模板按钮
            if st.button("添加默认模板"):
                with connect_db() as conn:
                    for i, template in enumerate(default_templates, 1):
                        conn.execute("""
                            INSERT INTO ReportTemplates (TemplateID, TemplateName, Description, Content)
                            VALUES (?, ?, ?, ?)
                        """, (i, template["name"], template["description"], template["content"]))
                    conn.commit()
                st.success("已添加默认模板")
                st.experimental_rerun()
    
    with template_tab2:  # 添加模板
        st.subheader("添加新模板")
        
        with st.form("add_template_form"):
            template_name = st.text_input("模板名称", placeholder="输入模板名称")
            description = st.text_area("模板描述", placeholder="输入模板描述")
            
            st.write("模板内容 (支持Markdown和以下变量):")
            st.code("""
        {{name}} - 姓名
        {{gender}} - 性别
        {{age}} - 年龄
        {{education_level}} - 教育水平
        {{percentage}} - 成绩百分比
        {{experience}} - 实习经验(月)
        {{english}} - 英语能力
        {{programming}} - 编程能力
        {{analytical}} - 分析能力
        {{teamwork}} - 团队协作
        {{communication}} - 沟通能力
        {{problem_solving}} - 解决问题能力
        {{skills_avg}} - 技能平均分
        {{predicted_salary}} - 预测薪资
        {{prediction_date}} - 预测日期
        {{market_comparison}} - 市场比较
            """)
            
            content = st.text_area("模板内容", height=300, placeholder="输入模板内容，支持Markdown和变量")
            
            submitted = st.form_submit_button("保存模板")
            
            if submitted:
                if template_name and content:
                    try:
                        with connect_db() as conn:
                            # 获取最大ID
                            max_id = conn.execute("SELECT MAX(TemplateID) FROM ReportTemplates").fetchone()[0]
                            new_id = 1 if max_id is None else max_id + 1
                            
                            # 插入新模板
                            conn.execute("""
                                INSERT INTO ReportTemplates (TemplateID, TemplateName, Description, Content)
                                VALUES (?, ?, ?, ?)
                            """, (new_id, template_name, description, content))
                            conn.commit()
                        
                        st.success(f"模板 '{template_name}' 已保存")
                        # 清空表单
                        st.experimental_rerun()
                    except Exception as e:
                        st.error(f"保存失败: {str(e)}")
                else:
                    st.error("模板名称和内容不能为空")
    
    with template_tab3:  # 编辑模板
        st.subheader("编辑现有模板")
        
        # 获取所有模板
        with connect_db() as conn:
            templates = pd.read_sql("SELECT TemplateID, TemplateName FROM ReportTemplates ORDER BY TemplateID", conn)
        
        if not templates.empty:
            selected_template_id = st.selectbox(
                "选择要编辑的模板", 
                templates["TemplateID"].tolist(),
                format_func=lambda x: f"{x}. {templates[templates['TemplateID']==x]['TemplateName'].iloc[0]}"
            )
            
            # 获取选定模板的详细信息
            with connect_db() as conn:
                template_details = pd.read_sql(f"SELECT * FROM ReportTemplates WHERE TemplateID = {selected_template_id}", conn).iloc[0]
            
            with st.form("edit_template_form"):
                edited_name = st.text_input("模板名称", value=template_details["TemplateName"])
                edited_description = st.text_area("模板描述", value=template_details["Description"])
                
                st.write("模板内容 (支持Markdown和变量):")
                edited_content = st.text_area("模板内容", value=template_details["Content"], height=300)
                
                submitted = st.form_submit_button("更新模板")
                
                if submitted:
                    if edited_name and edited_content:
                        try:
                            with connect_db() as conn:
                                conn.execute("""
                                    UPDATE ReportTemplates 
                                    SET TemplateName = ?, Description = ?, Content = ?, UpdatedAt = CURRENT_TIMESTAMP
                                    WHERE TemplateID = ?
                                """, (edited_name, edited_description, edited_content, selected_template_id))
                                conn.commit()
                            
                            st.success(f"模板 '{edited_name}' 已更新")
                            st.experimental_rerun()
                        except Exception as e:
                            st.error(f"更新失败: {str(e)}")
                    else:
                        st.error("模板名称和内容不能为空")
        else:
            st.info("暂无模板，请先添加模板")

# 添加使用手册模块
elif page == "使用手册":
    st.title("📚 系统使用手册")
    
    # 创建手册目录
    st.sidebar.markdown("## 目录")
    sections = ["系统概述", "数据管理", "CSV导入", "薪资预测", "报告模板管理", "常见问题"]
    selected_section = st.sidebar.radio("跳转到", sections)
    
    # 系统概述
    if selected_section == "系统概述":
        st.header("系统概述")
        st.markdown("""
        ### 工科毕业生薪酬预测自动化管理系统

        本系统是一个专为工科院校和企业设计的毕业生薪酬预测与管理平台。系统集成了数据管理、批量导入、薪资预测和报告生成等功能，旨在帮助用户高效管理毕业生信息并进行科学的薪资预测。

        ### 主要功能

        1. **数据管理**：添加、修改、删除和查询毕业生信息
        2. **CSV导入**：批量导入毕业生数据
        3. **薪资预测**：基于毕业生信息预测薪资水平
        4. **报告模板管理**：自定义和管理薪资预测报告模板

        ### 系统架构

        本系统采用Python语言开发，基于Streamlit框架构建用户界面，使用SQLite数据库存储数据。系统架构简洁高效，易于部署和维护。
        """)
        
        st.image("https://streamlit.io/images/brand/streamlit-logo-secondary-colormark-darktext.png", width=300)
        
        st.markdown("""
        ### 使用流程

        1. 首先在"数据管理"中添加毕业生信息，或通过"CSV导入"批量导入数据
        2. 在"报告模板管理"中创建或选择合适的报告模板
        3. 使用"薪资预测"功能，输入毕业生信息，生成薪资预测报告
        4. 下载或保存生成的报告

        ### 系统要求

        - Python 3.7+
        - Streamlit 1.10+
        - Pandas, NumPy, Plotly等依赖库
        - 现代Web浏览器（Chrome, Firefox, Edge等）
        """)
    
    # 数据管理
    elif selected_section == "数据管理":
        st.header("数据管理")
        st.markdown("""
        数据管理模块是系统的基础功能，用于管理毕业生的基本信息、教育背景和薪资数据。

        ### 新增记录

        在"新增记录"标签页中，您可以添加新的毕业生信息：

        1. 填写学号、性别、出生日期等基本信息
        2. 输入教育水平、成绩百分比等教育信息
        3. 设置薪资数据（如已知）
        4. 点击"提交"按钮保存信息

        ![新增记录](https://i.imgur.com/example1.png)

        ### 修改记录

        在"修改记录"标签页中，您可以更新现有毕业生的信息：

        1. 从下拉菜单中选择要修改的学生
        2. 修改相应字段的值
        3. 点击"更新"按钮保存修改

        ### 删除记录

        在"删除记录"标签页中，您可以删除不需要的毕业生记录：

        1. 从下拉菜单中选择要删除的学生
        2. 点击"删除"按钮
        3. 确认删除操作

        ### 综合查询

        在"综合查询"标签页中，您可以根据多种条件查询毕业生信息：

        1. 输入学号（可选）进行精确查询
        2. 选择性别、日期范围等筛选条件
        3. 设置最低成绩百分比和最低薪资（如需要）
        4. 点击"执行查询"按钮获取结果

        ### 数据初始化

        在"数据初始化"标签页中，您可以清空数据库中的数据：

        1. 选择要清空的表
        2. 输入"CONFIRM"确认操作
        3. 点击"执行初始化"按钮

        > **注意**：数据初始化操作不可撤销，请谨慎使用！
        """)
    
    # CSV导入
    elif selected_section == "CSV导入":
        st.header("CSV导入")
        st.markdown("""
        CSV导入模块允许您批量导入毕业生数据，大大提高数据录入效率。

        ### 导入步骤

        1. **上传CSV文件**：
           - 点击"浏览文件"按钮选择CSV文件
           - 系统支持.csv和.xlsx格式
           - 文件大小限制为200MB

        2. **预览数据**：
           - 上传成功后，系统会显示数据预览
           - 检查数据是否正确加载

        3. **数据处理**：
           - 系统会自动进行数据清洗和标准化
           - 处理空值、标准化字段格式、转换日期等

        4. **导入数据库**：
           - 选择导入模式（追加或替换）
           - 点击"导入到数据库"按钮
           - 系统会将数据分表存储到相应的数据库表中

        ### CSV文件格式要求

        为确保导入成功，您的CSV文件应包含以下必要字段：

        - **StudentID**：学生ID（必须唯一）
        - **Gender**：性别（M或F）
        - **DOB**：出生日期（YYYY-MM-DD格式）

        其他可选字段包括：

        - **EducationLevel**：教育水平
        - **Percentage**：成绩百分比
        - **Board**：教育委员会/学校
        - **Salary**：薪资

        ### 示例CSV格式

        ```
        StudentID,Gender,DOB,EducationLevel,Percentage,Board,Salary
        1001,M,1998-05-15,Graduate,85.5,清华大学,60000
        1002,F,1999-03-22,Masters,92.0,北京大学,75000
        ```

        ### 常见问题

        - **导入失败**：检查CSV格式是否正确，确保必要字段存在
        - **重复数据**：系统会检查StudentID的唯一性，重复ID会导致导入失败
        - **日期格式**：确保日期格式为YYYY-MM-DD
        """)
    
    # 薪资预测
    elif selected_section == "薪资预测":
        st.header("薪资预测")
        st.markdown("""
        薪资预测模块是系统的核心功能，它基于毕业生的个人信息、教育背景和技能评估，预测其在就业市场中的薪资水平。

        ### 使用步骤

        1. **输入毕业生信息**：
           - 填写姓名、性别、出生日期等基本信息
           - 输入学号、联系电话、电子邮箱等联系方式（选填）
           - 选择教育水平、成绩百分比、学校、专业等教育信息
           - 设置实习经验月数

        2. **技能评估**：
           - 对英语能力、编程能力、分析能力等进行评分（1-10分）
           - 这些评分将直接影响薪资预测结果

        3. **项目经验与证书**：
           - 描述项目经验（如有）
           - 列出获得的证书和奖项（如有）

        4. **选择报告模板**：
           - 从下拉菜单中选择合适的报告模板
           - 不同模板提供不同格式和详细程度的报告

        5. **生成预测报告**：
           - 点击"生成预测报告"按钮
           - 系统会基于输入信息计算预测薪资
           - 生成可视化报告

        6. **下载报告**：
           - 预览生成的报告
           - 点击"下载报告"按钮保存为HTML文件

        ### 预测模型说明

        本系统采用多因素综合评估模型进行薪资预测，主要考虑以下因素：

        - **基本薪资**：行业基准起薪
        - **教育加成**：基于教育水平的薪资加成
        - **成绩加成**：基于学业成绩的薪资加成
        - **经验加成**：基于实习经验的薪资加成
        - **技能加成**：基于各项技能评分的薪资加成
        - **项目经验加成**：基于项目经验的薪资加成
        - **证书加成**：基于证书和奖项的薪资加成

        ### 预测结果解读

        预测报告通常包含以下内容：

        - 个人基本信息摘要
        - 技能评估分析
        - 预测薪资金额
        - 市场比较分析
        - 职业发展建议
        - 薪资增长预测

        > **注意**：薪资预测结果仅供参考，实际薪资可能受多种因素影响，如地区、公司规模、经济环境等。
        """)
    
    # 报告模板管理
    elif selected_section == "报告模板管理":
        st.header("报告模板管理")
        st.markdown("""
        报告模板管理模块允许您创建、编辑和管理薪资预测报告的模板，以满足不同场景的需求。

        ### 查看模板

        在"查看模板"标签页中，您可以：

        1. 浏览所有现有模板
        2. 查看模板的详细信息，包括名称、描述、内容等
        3. 删除不需要的模板
        4. 如果没有模板，可以点击"添加默认模板"按钮添加系统预设的模板

        ### 添加模板

        在"添加模板"标签页中，您可以创建新的报告模板：

        1. 输入模板名称和描述
        2. 编写模板内容，支持Markdown格式和变量占位符
        3. 点击"保存模板"按钮

        ### 编辑模板

        在"编辑模板"标签页中，您可以修改现有模板：

        1. 从下拉菜单中选择要编辑的模板
        2. 修改模板名称、描述和内容
        3. 点击"更新模板"按钮保存修改

        ### 模板变量

        模板支持以下变量占位符，在生成报告时会自动替换为实际值：

        ```
        {{name}} - 姓名
        {{gender}} - 性别
        {{age}} - 年龄
        {{education_level}} - 教育水平
        {{percentage}} - 成绩百分比
        {{experience}} - 实习经验(月)
        {{english}} - 英语能力
        {{programming}} - 编程能力
        {{analytical}} - 分析能力
        {{teamwork}} - 团队协作
        {{communication}} - 沟通能力
        {{problem_solving}} - 解决问题能力
        {{skills_avg}} - 技能平均分
        {{predicted_salary}} - 预测薪资
        {{prediction_date}} - 预测日期
        {{market_comparison}} - 市场比较
        {{major}} - 专业
        {{board}} - 学校
        {{graduation_year}} - 毕业年份
        {{project_experience}} - 项目经验
        {{certificates}} - 证书和奖项
        ```

        ### 模板示例

        系统预设了多种报告模板，包括：

        1. **标准薪资预测报告**：基本的薪资预测报告
        2. **详细分析报告**：包含详细分析和市场比较的综合报告
        3. **简洁版报告**：简短的薪资预测摘要
        4. **职业发展报告**：包含职业发展路径和薪资增长预测
        5. **竞争力分析报告**：分析毕业生在就业市场中的竞争优势和劣势

        您可以基于这些模板进行修改，或创建全新的模板以满足特定需求。
        """)
    
    # 常见问题
    elif selected_section == "常见问题":
        st.header("常见问题")
        
        faq = {
            "如何批量导入数据？": "使用CSV导入功能，准备好符合格式要求的CSV文件，上传后按照系统提示完成导入。",
            
            "预测的薪资准确吗？": "系统基于多种因素进行预测，结果仅供参考。实际薪资受地区、公司规模、经济环境等多种因素影响。",
            
            "如何创建自定义报告模板？": "在报告模板管理中，选择'添加模板'，输入模板名称和描述，使用Markdown格式和变量占位符编写模板内容。",
            
            "系统支持哪些数据格式？": "系统支持CSV和Excel格式的数据导入，导出支持CSV和HTML格式。",
            
            "如何备份数据？": "系统使用SQLite数据库，您可以直接复制数据库文件进行备份。或者使用CSV导出功能导出数据。",
            
            "忘记学生ID怎么办？": "在综合查询中，您可以不输入学号，通过其他条件（如姓名、性别等）查询学生信息。",
            
            "如何删除所有数据？": "在数据管理的'数据初始化'标签页中，选择要清空的表，输入'CONFIRM'确认，点击'执行初始化'按钮。",
            
            "报告模板变量不生效怎么办？": "检查变量名是否正确，变量格式应为{{变量名}}，注意大小写。",
            
            "系统支持多用户同时使用吗？": "基础版本不支持多用户并发操作，如有需要，请联系开发者获取企业版。",
            
            "如何联系技术支持？": "如遇到技术问题，请发送邮件至support@example.com，或在工作时间拨打技术支持热线。"
        }
        
        for question, answer in faq.items():
            with st.expander(question):
                st.markdown(answer)
        
        st.info("如果您的问题未在此列出，请联系系统管理员或技术支持团队。")